{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# jvm2008数据源 贴源数据层 代码实现\n",
    "\n",
    "下述过程应当封装为完善的函数，后续组装时可以直接使用\n",
    "\n",
    "## 1. 下载最新的数据源\n",
    "\n",
    "以SPECjvm 2008为例，下载地址为\n",
    "https://www.spec.org/cgi-bin/osgresults?conf=jvm2008;op=dump;format=csvdump\n",
    "\n",
    "函数传入数据源的名称，可以下载5个数据源（cpu2017, cpu2006, jbb2015, jvm2008, power_sjj2008），下载地址如下\n",
    "\n",
    "https://www.spec.org/cgi-bin/osgresults?conf=cpu2017;op=dump;format=csvdump\n",
    "https://www.spec.org/cgi-bin/osgresults?conf=cpu2006;op=dump;format=csvdump\n",
    "https://www.spec.org/cgi-bin/osgresults?conf=jbb2015;op=dump;format=csvdump\n",
    "https://www.spec.org/cgi-bin/osgresults?conf=jvm2008;op=dump;format=csvdump\n",
    "https://www.spec.org/cgi-bin/osgresults?conf=power_ssj2008;op=dump;format=csvdump\n",
    "\n",
    "发现只有中间conf的部分存在差别，因此通过数据源的名称即可生成下载地址，如果传入参数有误则抛出异常（assert子句）"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import requests, os\n",
    "\n",
    "def download_latest_results(benchmark_name: str):\n",
    "    \"\"\"\n",
    "    下载最新的数据源\n",
    "    :param benchmark_name: 数据源名称，应当是cpu2017, cpu2006, jbb2015, jvm2008, power_ssj2008其中的一个\n",
    "    :return: 返回成功下载的文件路径名\n",
    "    \"\"\"\n",
    "    benchmark_list = [\"cpu2017\", \"cpu2006\", \"jbb2015\", \"jvm2008\", \"power_ssj2008\"]\n",
    "    assert benchmark_name in benchmark_list\n",
    "    # 根据数据源名称生成下载路径\n",
    "    download_addr = f\"https://www.spec.org/cgi-bin/osgresults?conf={ benchmark_name };op=dump;format=csvdump\"\n",
    "    try:\n",
    "        response = requests.get(download_addr, stream=True)\n",
    "        response.raise_for_status()\n",
    "        # 从response的header中获得文件名，这个文件名包含了数据源名称以及下载时的日期时间\n",
    "        file_name = response.headers[\"Content-Disposition\"].split(\";\")[1].split(\"=\")[1][1:-1]\n",
    "        print(file_name)\n",
    "        file_path = os.path.join(\"../data/raw\", file_name)\n",
    "        # 写入文件\n",
    "        with open(file_path, \"wb\") as f:\n",
    "            for chunk in response.iter_content(chunk_size=1048576):\n",
    "                if chunk:\n",
    "                    f.write(chunk)\n",
    "        return file_path\n",
    "    except Exception as e:\n",
    "        print(\"failed to download results\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "jvm2008-results-20220516-121221.csv\n"
     ]
    },
    {
     "data": {
      "text/plain": "'../data/raw\\\\jvm2008-results-20220516-121221.csv'"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "download_latest_results(\"jvm2008\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 2. 获得已经下载到本地的最新数据源\n",
    "\n",
    "如果多次下载，data文件夹下会存在多份文件，可以封装一个函数，传入数据源名称，从文件夹中返回最新的数据源文件路径，同时还另外传入一个参数，决定是否删除旧的数据源文件"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [],
   "source": [
    "def get_latest_results(benchmark_name: str, delete_old: bool = False):\n",
    "    \"\"\"\n",
    "    返回本地最新的数据源文件路径名\n",
    "    :param benchmark_name: 数据源名称，应当是cpu2017, cpu2006, jbb2015, jvm2008, power_ssj2008其中的一个\n",
    "    :param delete_old: 是否删除旧的数据源文件\n",
    "    :return: 返回最新的数据源文件路径名\n",
    "    \"\"\"\n",
    "    benchmark_list = [\"cpu2017\", \"cpu2006\", \"jbb2015\", \"jvm2008\", \"power_ssj2008\"]\n",
    "    assert benchmark_name in benchmark_list\n",
    "    all_file_path_list = os.listdir(\"../data/raw\")    # 获取data文件夹下所有文件名的列表\n",
    "    benchmark_file_path_list = [i for i in all_file_path_list if i.find(benchmark_name) != -1]    # 找到属于该数据源的所有文件\n",
    "    max_time = 0    # 记录数据源文件的下载时间\n",
    "    max_time_index = 0    # 记录数据源文件的在列表中的下标\n",
    "    for index, i in enumerate(benchmark_file_path_list):\n",
    "        time = int(i.split(\"-\")[-2] + i.split(\"-\")[-1].split(\".\")[0])\n",
    "        if time > max_time:    # 保留数值最大（即下载时间最新）的数据源文件下标\n",
    "            max_time = time\n",
    "            max_time_index = index\n",
    "    latest_file_path = benchmark_file_path_list[max_time_index]    # 最新的数据源文件名称\n",
    "    if delete_old:    # 如果需要删除旧的数据源文件\n",
    "        del benchmark_file_path_list[max_time_index]\n",
    "        for i in benchmark_file_path_list:\n",
    "            os.remove(os.path.join(\"../data/raw\", i))\n",
    "    return os.path.join(\"../data/raw\", latest_file_path)   # 生成路径名并返回"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "'../data/raw\\\\jvm2008-results-20220516-121221.csv'"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_latest_results(benchmark_name=\"jvm2008\", delete_old=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "以上两个步骤，其余数据集通用。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 3. 使用pandas读取CSV文件并整理成DataFrame\n",
    "\n",
    "从数据源文件（CSV格式）读取数据源并整理成CSV文件，便于后续处理，使用pandas库的pd.read_csv函数即可\n",
    "\n",
    "其中，文件路径名可以通过上面的get_lastest_results函数获取"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "      Benchmark                 Company  \\\n0   SPECjvm2008              Apple Inc.   \n1   SPECjvm2008              Apple Inc.   \n2   SPECjvm2008              Apple Inc.   \n3   SPECjvm2008  Huawei Technologies Co   \n4   SPECjvm2008      Oracle Corporation   \n5   SPECjvm2008      Oracle Corporation   \n6   SPECjvm2008      Oracle Corporation   \n7   SPECjvm2008                   Sugon   \n8   SPECjvm2008   Sun Microsystems Inc.   \n9   SPECjvm2008   Sun Microsystems Inc.   \n10  SPECjvm2008  Sun Microsystems, Inc.   \n\n                                   System  Result  \\\n0                       iMac (Early 2009)    26.4   \n1                       iMac (Early 2009)    37.4   \n2                       iMac (Early 2009)    51.6   \n3                                 RH 2285   336.0   \n4                        Netra SPARC T4-2   455.0   \n5                              SPARC T3-2   321.0   \n6                              SPARC T4-2   454.0   \n7   Sugon I620-G20(Intel Xeon E5-2660 v3)   853.0   \n8                         Sun Blade X6270   317.0   \n9                          Sun Fire X4450   284.0   \n10                         Sun Fire X4450   260.0   \n\n                                                  JVM  # cores  # chips  \\\n0   Java HotSpot(TM) Client VM 1.5.0_20-141 mixed ...        2        1   \n1   Java HotSpot(TM) Client VM 14.1-b02-90 mixed mode        2        1   \n2   Java HotSpot(TM) 64-Bit Server VM 14.1-b02-90 ...        2        1   \n3   Java Hotspot(TM) 64-Bit Server VM on Linux Jav...       12        2   \n4   Java HotSpot(TM) 64-Bit Server VM 22.0-b10 mix...       16        2   \n5   Java HotSpot(TM) 64-Bit Server VM on Solaris 1...       32        2   \n6   Java HotSpot(TM) 64-Bit Server VM 22.0-b08 mix...       16        2   \n7       OpenJDK 64-Bit Server VM 24.45-b08 mixed mode       20        2   \n8   Java HotSpot(TM) 64-Bit Server VM 1.6.0_14 Per...        8        2   \n9   Java HotSpot(TM) 64-Bit Server VM 1.6.0_06 Per...       24        4   \n10  Java Hotspot(TM) 64-Bit Server VM on Solaris 1...       16        4   \n\n    # cores per chip  Logical CPUs   \\\n0                  2              2   \n1                  2              2   \n2                  2              2   \n3                  6             24   \n4                  8            128   \n5                 16            256   \n6                  8            128   \n7                 10             40   \n8                  4             16   \n9                  6             24   \n10                 4             16   \n\n                                            Processor  ...  HW Avail  \\\n0                          Intel Core 2 Duo CPU E8335  ...  Mar-2009   \n1                          Intel Core 2 Duo CPU E8335  ...  Mar-2009   \n2                          Intel Core 2 Duo CPU E8335  ...  Mar-2009   \n3   Intel Xeon E5645 (Intel Turbo Boost Technology...  ...       NaN   \n4                                            SPARC T4  ...  Jan-2012   \n5                                            SPARC T3  ...  Dec-2010   \n6                                            SPARC T4  ...  Oct-2011   \n7   Intel Xeon E5-2660 v3(Intel Turbo Boost Techno...  ...       NaN   \n8   Intel Xeon X5570 (Intel Turbo Boost Technology...  ...  Apr-2009   \n9    Intel Xeon X7460 Quad Core (1066 MHz system bus)  ...  Oct-2008   \n10   Intel Xeon X7350 Quad Core (1066 MHz system bus)  ...  Mar-2008   \n\n    OS Avail  SW Avail License               Tested By Test Date Published  \\\n0   Aug-2009  Sep-2009      77              Apple Inc.  Oct-2009  Nov-2009   \n1   Sep-2009  Sep-2009      77              Apple Inc.  Oct-2009  Nov-2009   \n2   Sep-2009  Sep-2009      77              Apple Inc.  Oct-2009  Nov-2009   \n3   Mar-0025       NaN    3175  Huawei Technologies Co  Dec-2011  Jan-2012   \n4   Nov-2011  Dec-2011      73      Oracle Corporation  Jan-2012  Jan-2012   \n5   Sep-2010  Sep-2010      73      Oracle Corporation  Sep-2010  Oct-2010   \n6   Nov-2011  Dec-2011      73      Oracle Corporation  Oct-2011  Nov-2011   \n7        NaN       NaN    9046                   Sugon  Jan-2015  Feb-2015   \n8   Nov-2008  Jun-2009       6   Sun Microsystems Inc.  Mar-2009  Apr-2009   \n9   Oct-2008  Jul-2008       6        Sun Microsystems  Sep-2008  Sep-2008   \n10  Apr-2008  Jul-2008       6  Sun Microsystems, Inc.  Jun-2008  Jul-2008   \n\n    Updated                                          Disclosure  Disclosures  \n0   Nov-2009  <A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...          NaN  \n1   Nov-2009  <A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...          NaN  \n2   Nov-2009  <A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...          NaN  \n3   Jan-2012  <A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...          NaN  \n4   Jan-2012  <A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...          NaN  \n5   Oct-2010  <A HREF=\"/jvm2008/results/res2010q4/jvm2008-20...          NaN  \n6   Nov-2011  <A HREF=\"/jvm2008/results/res2011q4/jvm2008-20...          NaN  \n7   Feb-2015  <A HREF=\"/jvm2008/results/res2015q1/jvm2008-20...          NaN  \n8   Jun-2009  <A HREF=\"/jvm2008/results/res2009q2/jvm2008-20...          NaN  \n9   Sep-2008  <A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...          NaN  \n10  Jul-2008  <A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...          NaN  \n\n[11 rows x 26 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Benchmark</th>\n      <th>Company</th>\n      <th>System</th>\n      <th>Result</th>\n      <th>JVM</th>\n      <th># cores</th>\n      <th># chips</th>\n      <th># cores per chip</th>\n      <th>Logical CPUs</th>\n      <th>Processor</th>\n      <th>...</th>\n      <th>HW Avail</th>\n      <th>OS Avail</th>\n      <th>SW Avail</th>\n      <th>License</th>\n      <th>Tested By</th>\n      <th>Test Date</th>\n      <th>Published</th>\n      <th>Updated</th>\n      <th>Disclosure</th>\n      <th>Disclosures</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>SPECjvm2008</td>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>26.4</td>\n      <td>Java HotSpot(TM) Client VM 1.5.0_20-141 mixed ...</td>\n      <td>2</td>\n      <td>1</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>...</td>\n      <td>Mar-2009</td>\n      <td>Aug-2009</td>\n      <td>Sep-2009</td>\n      <td>77</td>\n      <td>Apple Inc.</td>\n      <td>Oct-2009</td>\n      <td>Nov-2009</td>\n      <td>Nov-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>SPECjvm2008</td>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>37.4</td>\n      <td>Java HotSpot(TM) Client VM 14.1-b02-90 mixed mode</td>\n      <td>2</td>\n      <td>1</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>...</td>\n      <td>Mar-2009</td>\n      <td>Sep-2009</td>\n      <td>Sep-2009</td>\n      <td>77</td>\n      <td>Apple Inc.</td>\n      <td>Oct-2009</td>\n      <td>Nov-2009</td>\n      <td>Nov-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>SPECjvm2008</td>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>51.6</td>\n      <td>Java HotSpot(TM) 64-Bit Server VM 14.1-b02-90 ...</td>\n      <td>2</td>\n      <td>1</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>...</td>\n      <td>Mar-2009</td>\n      <td>Sep-2009</td>\n      <td>Sep-2009</td>\n      <td>77</td>\n      <td>Apple Inc.</td>\n      <td>Oct-2009</td>\n      <td>Nov-2009</td>\n      <td>Nov-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>SPECjvm2008</td>\n      <td>Huawei Technologies Co</td>\n      <td>RH 2285</td>\n      <td>336.0</td>\n      <td>Java Hotspot(TM) 64-Bit Server VM on Linux Jav...</td>\n      <td>12</td>\n      <td>2</td>\n      <td>6</td>\n      <td>24</td>\n      <td>Intel Xeon E5645 (Intel Turbo Boost Technology...</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>Mar-0025</td>\n      <td>NaN</td>\n      <td>3175</td>\n      <td>Huawei Technologies Co</td>\n      <td>Dec-2011</td>\n      <td>Jan-2012</td>\n      <td>Jan-2012</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>SPECjvm2008</td>\n      <td>Oracle Corporation</td>\n      <td>Netra SPARC T4-2</td>\n      <td>455.0</td>\n      <td>Java HotSpot(TM) 64-Bit Server VM 22.0-b10 mix...</td>\n      <td>16</td>\n      <td>2</td>\n      <td>8</td>\n      <td>128</td>\n      <td>SPARC T4</td>\n      <td>...</td>\n      <td>Jan-2012</td>\n      <td>Nov-2011</td>\n      <td>Dec-2011</td>\n      <td>73</td>\n      <td>Oracle Corporation</td>\n      <td>Jan-2012</td>\n      <td>Jan-2012</td>\n      <td>Jan-2012</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>SPECjvm2008</td>\n      <td>Oracle Corporation</td>\n      <td>SPARC T3-2</td>\n      <td>321.0</td>\n      <td>Java HotSpot(TM) 64-Bit Server VM on Solaris 1...</td>\n      <td>32</td>\n      <td>2</td>\n      <td>16</td>\n      <td>256</td>\n      <td>SPARC T3</td>\n      <td>...</td>\n      <td>Dec-2010</td>\n      <td>Sep-2010</td>\n      <td>Sep-2010</td>\n      <td>73</td>\n      <td>Oracle Corporation</td>\n      <td>Sep-2010</td>\n      <td>Oct-2010</td>\n      <td>Oct-2010</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2010q4/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>SPECjvm2008</td>\n      <td>Oracle Corporation</td>\n      <td>SPARC T4-2</td>\n      <td>454.0</td>\n      <td>Java HotSpot(TM) 64-Bit Server VM 22.0-b08 mix...</td>\n      <td>16</td>\n      <td>2</td>\n      <td>8</td>\n      <td>128</td>\n      <td>SPARC T4</td>\n      <td>...</td>\n      <td>Oct-2011</td>\n      <td>Nov-2011</td>\n      <td>Dec-2011</td>\n      <td>73</td>\n      <td>Oracle Corporation</td>\n      <td>Oct-2011</td>\n      <td>Nov-2011</td>\n      <td>Nov-2011</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2011q4/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>SPECjvm2008</td>\n      <td>Sugon</td>\n      <td>Sugon I620-G20(Intel Xeon E5-2660 v3)</td>\n      <td>853.0</td>\n      <td>OpenJDK 64-Bit Server VM 24.45-b08 mixed mode</td>\n      <td>20</td>\n      <td>2</td>\n      <td>10</td>\n      <td>40</td>\n      <td>Intel Xeon E5-2660 v3(Intel Turbo Boost Techno...</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>9046</td>\n      <td>Sugon</td>\n      <td>Jan-2015</td>\n      <td>Feb-2015</td>\n      <td>Feb-2015</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2015q1/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>SPECjvm2008</td>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Blade X6270</td>\n      <td>317.0</td>\n      <td>Java HotSpot(TM) 64-Bit Server VM 1.6.0_14 Per...</td>\n      <td>8</td>\n      <td>2</td>\n      <td>4</td>\n      <td>16</td>\n      <td>Intel Xeon X5570 (Intel Turbo Boost Technology...</td>\n      <td>...</td>\n      <td>Apr-2009</td>\n      <td>Nov-2008</td>\n      <td>Jun-2009</td>\n      <td>6</td>\n      <td>Sun Microsystems Inc.</td>\n      <td>Mar-2009</td>\n      <td>Apr-2009</td>\n      <td>Jun-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q2/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>SPECjvm2008</td>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Fire X4450</td>\n      <td>284.0</td>\n      <td>Java HotSpot(TM) 64-Bit Server VM 1.6.0_06 Per...</td>\n      <td>24</td>\n      <td>4</td>\n      <td>6</td>\n      <td>24</td>\n      <td>Intel Xeon X7460 Quad Core (1066 MHz system bus)</td>\n      <td>...</td>\n      <td>Oct-2008</td>\n      <td>Oct-2008</td>\n      <td>Jul-2008</td>\n      <td>6</td>\n      <td>Sun Microsystems</td>\n      <td>Sep-2008</td>\n      <td>Sep-2008</td>\n      <td>Sep-2008</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>SPECjvm2008</td>\n      <td>Sun Microsystems, Inc.</td>\n      <td>Sun Fire X4450</td>\n      <td>260.0</td>\n      <td>Java Hotspot(TM) 64-Bit Server VM on Solaris 1...</td>\n      <td>16</td>\n      <td>4</td>\n      <td>4</td>\n      <td>16</td>\n      <td>Intel Xeon X7350 Quad Core (1066 MHz system bus)</td>\n      <td>...</td>\n      <td>Mar-2008</td>\n      <td>Apr-2008</td>\n      <td>Jul-2008</td>\n      <td>6</td>\n      <td>Sun Microsystems, Inc.</td>\n      <td>Jun-2008</td>\n      <td>Jul-2008</td>\n      <td>Jul-2008</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>11 rows × 26 columns</p>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "jvm2008_data = pd.read_csv(get_latest_results(benchmark_name=\"jvm2008\"))\n",
    "jvm2008_data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 4. 贴源数据层的实现：从数据源中抽取相关的属性并聚合\n",
    "\n",
    "尝试对数据进行一些处理，例如对同一个生产厂家的机器做一个聚合，从生产厂家的角度考察机器的Java性能"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.1 抽取感兴趣的属性\n",
    "\n",
    "感兴趣的属性：\n",
    "- Company机器生产公司\n",
    "- System系统型号\n",
    "- Result得分\n",
    "- \\# cores核心数\n",
    "- Processor处理器型号【是统一的数据主体，合并数据时以该属性作为准则】\n",
    "- CPU频率\n",
    "- 各级Cache容量：1st Cache, 2nd Cache, Other Cache\n",
    "- Memory存储大小\n",
    "- Updated最后分数记录时间\n",
    "- Disclosure详细结果报告的链接"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# jvm2008数据源，感兴趣的属性\n",
    "ods_jvm2008_col = [\"Company\", \"System\", \"Result\", \"# cores\", \"# cores per chip\", \"Processor\", \"CPU Speed\", \"1st Cache\", \"2nd Cache\", \"Other Cache\", \"Memory\", \"Updated \", \"Disclosure\"]\n",
    "\n",
    "# 抽取相关属性，构成DataFrame，前缀为ods表明为贴源数据层（其中# cores per chip为中间数据，数据清洗完成后应当删去）\n",
    "ods_jvm2008_data = pd.read_csv(get_latest_results(benchmark_name=\"jvm2008\"), usecols=ods_jvm2008_col)\n",
    "\n",
    "# 更新部分属性名\n",
    "ods_jvm2008_data = ods_jvm2008_data.rename(columns={\"Updated \": \"Updated\"})\n",
    "\n",
    "ods_jvm2008_data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "                   Company                                 System  Result  \\\n0               Apple Inc.                      iMac (Early 2009)    26.4   \n1               Apple Inc.                      iMac (Early 2009)    37.4   \n2               Apple Inc.                      iMac (Early 2009)    51.6   \n3   Huawei Technologies Co                                RH 2285   336.0   \n4       Oracle Corporation                       Netra SPARC T4-2   455.0   \n5       Oracle Corporation                             SPARC T3-2   321.0   \n6       Oracle Corporation                             SPARC T4-2   454.0   \n7                    Sugon  Sugon I620-G20(Intel Xeon E5-2660 v3)   853.0   \n8    Sun Microsystems Inc.                        Sun Blade X6270   317.0   \n9    Sun Microsystems Inc.                         Sun Fire X4450   284.0   \n10  Sun Microsystems, Inc.                         Sun Fire X4450   260.0   \n\n    # cores  # cores per chip  \\\n0         2                 2   \n1         2                 2   \n2         2                 2   \n3        12                 6   \n4        16                 8   \n5        32                16   \n6        16                 8   \n7        20                10   \n8         8                 4   \n9        24                 6   \n10       16                 4   \n\n                                            Processor  CPU Speed  \\\n0                          Intel Core 2 Duo CPU E8335    2930.00   \n1                          Intel Core 2 Duo CPU E8335    2930.00   \n2                          Intel Core 2 Duo CPU E8335    2930.00   \n3   Intel Xeon E5645 (Intel Turbo Boost Technology...       2.40   \n4                                            SPARC T4    2848.00   \n5                                            SPARC T3       1.65   \n6                                            SPARC T4    2848.00   \n7   Intel Xeon E5-2660 v3(Intel Turbo Boost Techno...    2600.00   \n8   Intel Xeon X5570 (Intel Turbo Boost Technology...       2.93   \n9    Intel Xeon X7460 Quad Core (1066 MHz system bus)    2667.00   \n10   Intel Xeon X7350 Quad Core (1066 MHz system bus)    2933.00   \n\n                               1st Cache  \\\n0      32KB(I)+32KB(D) on chip, per core   \n1      32KB(I)+32KB(D) on chip, per core   \n2      32KB(I)+32KB(D) on chip, per core   \n3           32KBI+32KBD on chip per core   \n4               16KB(I)+16KB(D) per core   \n5                16KB(I)+8KB(D) per core   \n6               16KB(I)+16KB(D) per core   \n7                                  32 KB   \n8           32KBI+32KBD on ship per core   \n9   32KB(I) + 32KB (D) on chip, per core   \n10  32KB(I) + 32KB (D) on chip, per core   \n\n                                     2nd Cache                   Other Cache  \\\n0                                    6MB (I+D)                           NaN   \n1                                    6MB (I+D)                           NaN   \n2                                    6MB (I+D)                           NaN   \n3                 256KB(I+D) on chip, per core  12MB (I+D) on chip, per chip   \n4                        128 KB (I+D) per core           4 MB (I+D) per chip   \n5                                6 MB per chip                           NaN   \n6                        128 KB (I+D) per core           4 MB (I+D) per chip   \n7                                       256 KB                         25 MB   \n8                  256 KB I+D on chip per core   8MB I+D L3 on chip per chip   \n9   9 MB I+D on chips (3MB shared per 2 cores)            HW_CPU_CACHE_OTHER   \n10  8 MB I+D on chips (4MB shared per 2 cores)                           NaN   \n\n    Memory   Updated                                         Disclosure  \n0   4096MB  Nov-2009  <A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...  \n1   4096MB  Nov-2009  <A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...  \n2   4096MB  Nov-2009  <A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...  \n3     48GB  Jan-2012  <A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...  \n4   262144  Jan-2012  <A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...  \n5   256 GB  Oct-2010  <A HREF=\"/jvm2008/results/res2010q4/jvm2008-20...  \n6   262144  Nov-2011  <A HREF=\"/jvm2008/results/res2011q4/jvm2008-20...  \n7   256 GB  Feb-2015  <A HREF=\"/jvm2008/results/res2015q1/jvm2008-20...  \n8     48GB  Jun-2009  <A HREF=\"/jvm2008/results/res2009q2/jvm2008-20...  \n9    65536  Sep-2008  <A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...  \n10   65536  Jul-2008  <A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Company</th>\n      <th>System</th>\n      <th>Result</th>\n      <th># cores</th>\n      <th># cores per chip</th>\n      <th>Processor</th>\n      <th>CPU Speed</th>\n      <th>1st Cache</th>\n      <th>2nd Cache</th>\n      <th>Other Cache</th>\n      <th>Memory</th>\n      <th>Updated</th>\n      <th>Disclosure</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>26.4</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>2930.00</td>\n      <td>32KB(I)+32KB(D) on chip, per core</td>\n      <td>6MB (I+D)</td>\n      <td>NaN</td>\n      <td>4096MB</td>\n      <td>Nov-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>37.4</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>2930.00</td>\n      <td>32KB(I)+32KB(D) on chip, per core</td>\n      <td>6MB (I+D)</td>\n      <td>NaN</td>\n      <td>4096MB</td>\n      <td>Nov-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>51.6</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>2930.00</td>\n      <td>32KB(I)+32KB(D) on chip, per core</td>\n      <td>6MB (I+D)</td>\n      <td>NaN</td>\n      <td>4096MB</td>\n      <td>Nov-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q4/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Huawei Technologies Co</td>\n      <td>RH 2285</td>\n      <td>336.0</td>\n      <td>12</td>\n      <td>6</td>\n      <td>Intel Xeon E5645 (Intel Turbo Boost Technology...</td>\n      <td>2.40</td>\n      <td>32KBI+32KBD on chip per core</td>\n      <td>256KB(I+D) on chip, per core</td>\n      <td>12MB (I+D) on chip, per chip</td>\n      <td>48GB</td>\n      <td>Jan-2012</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Oracle Corporation</td>\n      <td>Netra SPARC T4-2</td>\n      <td>455.0</td>\n      <td>16</td>\n      <td>8</td>\n      <td>SPARC T4</td>\n      <td>2848.00</td>\n      <td>16KB(I)+16KB(D) per core</td>\n      <td>128 KB (I+D) per core</td>\n      <td>4 MB (I+D) per chip</td>\n      <td>262144</td>\n      <td>Jan-2012</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2012q1/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Oracle Corporation</td>\n      <td>SPARC T3-2</td>\n      <td>321.0</td>\n      <td>32</td>\n      <td>16</td>\n      <td>SPARC T3</td>\n      <td>1.65</td>\n      <td>16KB(I)+8KB(D) per core</td>\n      <td>6 MB per chip</td>\n      <td>NaN</td>\n      <td>256 GB</td>\n      <td>Oct-2010</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2010q4/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Oracle Corporation</td>\n      <td>SPARC T4-2</td>\n      <td>454.0</td>\n      <td>16</td>\n      <td>8</td>\n      <td>SPARC T4</td>\n      <td>2848.00</td>\n      <td>16KB(I)+16KB(D) per core</td>\n      <td>128 KB (I+D) per core</td>\n      <td>4 MB (I+D) per chip</td>\n      <td>262144</td>\n      <td>Nov-2011</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2011q4/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Sugon</td>\n      <td>Sugon I620-G20(Intel Xeon E5-2660 v3)</td>\n      <td>853.0</td>\n      <td>20</td>\n      <td>10</td>\n      <td>Intel Xeon E5-2660 v3(Intel Turbo Boost Techno...</td>\n      <td>2600.00</td>\n      <td>32 KB</td>\n      <td>256 KB</td>\n      <td>25 MB</td>\n      <td>256 GB</td>\n      <td>Feb-2015</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2015q1/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Blade X6270</td>\n      <td>317.0</td>\n      <td>8</td>\n      <td>4</td>\n      <td>Intel Xeon X5570 (Intel Turbo Boost Technology...</td>\n      <td>2.93</td>\n      <td>32KBI+32KBD on ship per core</td>\n      <td>256 KB I+D on chip per core</td>\n      <td>8MB I+D L3 on chip per chip</td>\n      <td>48GB</td>\n      <td>Jun-2009</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2009q2/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Fire X4450</td>\n      <td>284.0</td>\n      <td>24</td>\n      <td>6</td>\n      <td>Intel Xeon X7460 Quad Core (1066 MHz system bus)</td>\n      <td>2667.00</td>\n      <td>32KB(I) + 32KB (D) on chip, per core</td>\n      <td>9 MB I+D on chips (3MB shared per 2 cores)</td>\n      <td>HW_CPU_CACHE_OTHER</td>\n      <td>65536</td>\n      <td>Sep-2008</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Sun Microsystems, Inc.</td>\n      <td>Sun Fire X4450</td>\n      <td>260.0</td>\n      <td>16</td>\n      <td>4</td>\n      <td>Intel Xeon X7350 Quad Core (1066 MHz system bus)</td>\n      <td>2933.00</td>\n      <td>32KB(I) + 32KB (D) on chip, per core</td>\n      <td>8 MB I+D on chips (4MB shared per 2 cores)</td>\n      <td>NaN</td>\n      <td>65536</td>\n      <td>Jul-2008</td>\n      <td>&lt;A HREF=\"/jvm2008/results/res2008q3/jvm2008-20...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.2 数据转换和清洗\n",
    "\n",
    "引入需要的库，后续对每一个属性的值，写出一个转换函数，即原始值X到转换后的值Y的函数，这个函数可以套用到DataFrame的map方法或者apply方法，用于批量操作"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [],
   "source": [
    "import re\n",
    "from typing import Union"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "\n",
    "#### 1. Company属性\n",
    "\n",
    "1. 例如“Sun Microsystems”和“Sun Microsystems, Inc.”，应当统一为不含逗号的\n",
    "2. 例如“Huawei Technologies Co”和“Oracle Corporation”，“Co”是简称，应当扩展为“Corporation”\n",
    "3. 其余的缩写，例如“Inc.”则不加以处理"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [],
   "source": [
    "def company_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Company这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Company属性的值\n",
    "    :return: 处理后的值\n",
    "    \"\"\"\n",
    "    y = x.replace(\",\", \"\")    # 去除逗号\n",
    "    y = re.sub(r\"\\bCo$\", \"Corporation\", y)    # 正则表达式替换：\\b匹配单词边界，$匹配字符串末尾\n",
    "    return y"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "\n",
    "#### 2. System属性：不处理\n",
    "\n",
    "#### 3. Result属性：不处理\n",
    "\n",
    "#### 4. \\# cores属性：不处理\n",
    "\n",
    "#### 5. Processor属性\n",
    "\n",
    "应当向cpu2017数据源的Processor属性的取值看齐\n",
    "\n",
    "1. 例如“Intel Xeon E5-2660 v3(Intel Turbo Boost Technology up to 3.30 GHz)”或“Intel Xeon X5570 (Intel Turbo Boost Technology up to 3.33GHz)”括号内内容应当删去，且删除后字符串末尾的空格也应当删去\n",
    "2. 例如“Intel Xeon X7350 Quad Core (1066 MHz system bus)”，为了保证格式的统一，Quad Core应当删去（这里指的是一个chip上有4个core，我们已经有\\# cores属性，这一点我们不关心），且删除后字符串末尾的空格也应当删去"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [],
   "source": [
    "def processor_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Processor这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Processor属性的值\n",
    "    :return: 处理后的值\n",
    "    \"\"\"\n",
    "    y = re.sub(r\"\\(.*\\)\", \"\", x)    # 删除括号中的内容\n",
    "    y = y.replace(\"Quad Core\", \"\")    # 删除”Quad Core“\n",
    "    y = y.rstrip()    # 处理删除之后，末尾留下的空格\n",
    "    return y"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 6. CPU Speed属性\n",
    "\n",
    "部分数据单位不统一，例如“2.93”，结合上下文可知这个单位是GHz，其余的大多为MHz，应当统一为MHz，因此设置一个规则，数值小于10的，将该数值乘1000化为Hz，且数据类型化为int型"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [],
   "source": [
    "def cpu_speed_rule(x: Union[int, float]) -> int:\n",
    "    \"\"\"\n",
    "    对CPU Speed这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: CPU Speed属性的值\n",
    "    :return: 处理后的CPU Speed(MHz)值\n",
    "    \"\"\"\n",
    "    return int(x if x > 10 else 1000 * x)    # 统一单位为MHz，对单位是GHz的（数值小于10），乘以1000"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 7. Cache相关属性\n",
    "\n",
    "1. 1st Cache属性，有2种情况：\n",
    "    1. \\[case 1\\] 分icache和dcache（例如”32KB(I)+32KB(D) on chip, per core“），那么应当整理为”XXKB(I)+XXKB(D)“，这里都是per core的大小（如果计算总大小，应当乘以core数量）\n",
    "    2. \\[case 2\\] 不分icache和dcache（例如”32 KB“），那么应当整理为”XXKB(I+D)“\n",
    "2. 2nd Cache属性，二级缓存都是不分的，那么应当整理为”XXKB“，这里都是per core的大小，有多种情况：\n",
    "    1. \\[case 1\\] 例如”6MB (I+D)“，以MB作为单位的，需要给出\"\\# cores\"数量，例如2，那么是3MB per core，最终化为”3072KB“，但是如果是以KB作为单位的，例如”256 KB“，那么这个就是per core的大小，化为”256KB“即可\n",
    "    2. \\[case 2\\] 例如”256KB(I+D) on chip, per core“和”128 KB (I+D) per core“，出现了”per core“的，这里直接化为”256KB“和”128KB“即可\n",
    "    3. \\[case 3\\] 例如”9 MB I+D on chips (3MB shared per 2 cores)“，用括号内的计算，得到1.5MB per core，最终化为”1536KB“\n",
    "3. Other Cache属性，考虑到其他数据源既有Other Cache也有3rd Cache，这里应当予以区分，全部化为per chip\n",
    "   1. \\[case 1\\] 例如缺失值NaN或者例如“HW_CPU_CACHE_OTHER”含义不明的，Other Cache和3rd Cache都应当为0\n",
    "   2. \\[case 2\\] 内容中提到了“L3”，例如“8MB I+D L3 on chip per chip”，应当在3rd Cache中记录为“8MB”\n",
    "   3. \\[case 3\\] 其余情况，例如“12MB (I+D) on chip, per chip”，提到了“per chip”，应当在Other Cache中记录为“12MB”"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [],
   "source": [
    "def first_cache_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对1st Cache这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: 1st Cache属性的值\n",
    "    :return: 处理后的1st Cache per core(KB)值\n",
    "    \"\"\"\n",
    "    if x.find(\"per core\") != -1:\n",
    "        # 说明：(group1: 1个以上数字)+'KB'+可能有空格+'(I)'或'I'+可能有空格+'+'+可能有空格+(group2: 1个以上数字)+'KB'+可能有空格+'(D)'或'D'\n",
    "        search_obj = re.match(r\"(\\d+)KB\\s*(?:\\(I\\)|I)\\s*\\+\\s*(\\d+)KB\\s*(?:\\(D\\)|D)\", x)\n",
    "        l1i_kb = search_obj.group(1)\n",
    "        l1d_kb = search_obj.group(2)\n",
    "        return f\"{l1i_kb}KB(I)+{l1d_kb}KB(D)\"\n",
    "    else:\n",
    "        # 说明：(group1: 1个以上数字)+可能有空格+'KB'\n",
    "        search_obj = re.match(r\"(\\d+)\\s*KB\", x)\n",
    "        l1_kb = search_obj.group(1)\n",
    "        return f\"{l1_kb}KB(I+D)\"\n",
    "\n",
    "def second_cache_rule(line: pd.Series) -> str:\n",
    "    \"\"\"\n",
    "    对2nd Cache这一列数据的处理操作，用于DataFrame的apply方法（需要用到其他属性的值）\n",
    "    :param line: DataFrame的一行，类型是pd.Series\n",
    "    :return: 处理后的2nd Cache per core(KB)值\n",
    "    \"\"\"\n",
    "    x = line[\"2nd Cache\"]\n",
    "    cores_per_chip = line[\"# cores per chip\"]\n",
    "    cores_num = line[\"# cores\"]\n",
    "    if x.find(\"per core\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*KB\", x)\n",
    "        l2_kb = search_obj.group(1)\n",
    "        return f\"{l2_kb}KB\"\n",
    "    elif x.find(\"per chip\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "        l2_kb = int((int(search_obj.group(1)) * 1024) / cores_per_chip)\n",
    "        return f\"{l2_kb}KB\"\n",
    "    elif x.find(\"shared per 2 cores\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "        l2_kb = int((int(search_obj.group(1)) * 1024) / cores_per_chip)\n",
    "        return f\"{l2_kb}KB\"\n",
    "    else:\n",
    "        if x.find(\"MB\") != -1:\n",
    "            search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "            l2_kb = int((int(search_obj.group(1)) * 1024) / cores_num)\n",
    "            return f\"{l2_kb}KB\"\n",
    "        else:\n",
    "            search_obj = re.match(r\"(\\d+)\\s*KB\", x)\n",
    "            return f\"{search_obj.group(1)}KB\"\n",
    "\n",
    "def third_cache_rule(x: Union[str, float]) -> str:\n",
    "    \"\"\"\n",
    "    对Other Cache这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: 1st Cache属性的值由于数据可能是缺失值（即NaN，float型）也可能是字符串\n",
    "    :return: 处理后的3rd Cache per chip(MB)值\n",
    "    \"\"\"\n",
    "    if type(x) == str:\n",
    "        if x.find(\"L3\") != -1:\n",
    "            search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "            return f\"{search_obj.group(1)}MB\"\n",
    "        else:\n",
    "            return \"0\"\n",
    "    else:\n",
    "        return \"0\"\n",
    "\n",
    "def other_cache_rule(x: Union[str, float]) -> str:\n",
    "    \"\"\"\n",
    "    对Other Cache这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: 1st Cache属性的值由于数据可能是缺失值（即NaN，float型）也可能是字符串\n",
    "    :return: 处理后的Other Cache per chip(MB)值\n",
    "    \"\"\"\n",
    "    if type(x) == str:\n",
    "        if x.find(\"L3\") == -1 and x.find(\"MB\") != -1:\n",
    "            search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "            return f\"{search_obj.group(1)}MB\"\n",
    "        else:\n",
    "            return \"0\"\n",
    "    else:\n",
    "        return \"0\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 8. Memory属性\n",
    "\n",
    "数据单位统一为“GB”，即“XXGB”的格式，将其中单位为“MB”的化为“GB”，部分没有单位的，其单位视为“MB”。例如“4096MB”应当化为“4GB”，例如“262144”应当化为“256GB”"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [],
   "source": [
    "def memory_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Memory这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Memory属性的值\n",
    "    :return: 处理后的Memory(GB)值\n",
    "    \"\"\"\n",
    "    if x.find(\"MB\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "        memory_gb = int(int(search_obj.group(1)) / 1024)\n",
    "        return f\"{memory_gb}GB\"\n",
    "    elif x.find(\"GB\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*GB\", x)\n",
    "        return f\"{search_obj.group(1)}GB\"\n",
    "    else:\n",
    "        return f\"{int(int(x) / 1024)}GB\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 9. Updated属性：不处理\n",
    "\n",
    "#### 10. Disclosure属性\n",
    "\n",
    "给出了HTML和Text格式的报告链接，其中链接指向HTML的详细信息页面，将对应超链接的地址和“https://www.spec.org”拼接，得到对应完整链接，即\n",
    "\n",
    "\"<A HREF=\"\"/jvm2008/results/res2008q3/jvm2008-20080617-00001.html\"\">HTML</A> <A HREF=\"\"/jvm2008/results/res2008q3/jvm2008-20080617-00001.txt\"\">Text</A>\""
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [],
   "source": [
    "def report_link_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Disclosure这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Disclosure属性的值\n",
    "    :return: 处理后的Report Link值\n",
    "    \"\"\"\n",
    "    search_obj = re.match(r'<A HREF=\"(.*)\">HTML</A>',x)\n",
    "    return f\"https://www.spec.org{search_obj.group(1)}\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "逐一应用上述方法"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "                            Company                                 System  \\\n0                        Apple Inc.                      iMac (Early 2009)   \n1                        Apple Inc.                      iMac (Early 2009)   \n2                        Apple Inc.                      iMac (Early 2009)   \n3   Huawei Technologies Corporation                                RH 2285   \n4                Oracle Corporation                       Netra SPARC T4-2   \n5                Oracle Corporation                             SPARC T3-2   \n6                Oracle Corporation                             SPARC T4-2   \n7                             Sugon  Sugon I620-G20(Intel Xeon E5-2660 v3)   \n8             Sun Microsystems Inc.                        Sun Blade X6270   \n9             Sun Microsystems Inc.                         Sun Fire X4450   \n10            Sun Microsystems Inc.                         Sun Fire X4450   \n\n    Result  # cores                   Processor  CPU Speed(MHz)  \\\n0     26.4        2  Intel Core 2 Duo CPU E8335            2930   \n1     37.4        2  Intel Core 2 Duo CPU E8335            2930   \n2     51.6        2  Intel Core 2 Duo CPU E8335            2930   \n3    336.0       12            Intel Xeon E5645            2400   \n4    455.0       16                    SPARC T4            2848   \n5    321.0       32                    SPARC T3            1650   \n6    454.0       16                    SPARC T4            2848   \n7    853.0       20       Intel Xeon E5-2660 v3            2600   \n8    317.0        8            Intel Xeon X5570            2930   \n9    284.0       24            Intel Xeon X7460            2667   \n10   260.0       16            Intel Xeon X7350            2933   \n\n   1st Cache per core(KB) 2nd Cache per core(KB) 3rd Cache per chip(MB)  \\\n0         32KB(I)+32KB(D)                 3072KB                      0   \n1         32KB(I)+32KB(D)                 3072KB                      0   \n2         32KB(I)+32KB(D)                 3072KB                      0   \n3         32KB(I)+32KB(D)                  256KB                      0   \n4         16KB(I)+16KB(D)                  128KB                      0   \n5          16KB(I)+8KB(D)                  384KB                      0   \n6         16KB(I)+16KB(D)                  128KB                      0   \n7               32KB(I+D)                  256KB                      0   \n8         32KB(I)+32KB(D)                  256KB                    8MB   \n9         32KB(I)+32KB(D)                 1536KB                      0   \n10        32KB(I)+32KB(D)                 2048KB                      0   \n\n   Other Cache per chip(MB) Memory(GB)   Updated  \\\n0                         0        4GB  Nov-2009   \n1                         0        4GB  Nov-2009   \n2                         0        4GB  Nov-2009   \n3                      12MB       48GB  Jan-2012   \n4                       4MB      256GB  Jan-2012   \n5                         0      256GB  Oct-2010   \n6                       4MB      256GB  Nov-2011   \n7                      25MB      256GB  Feb-2015   \n8                         0       48GB  Jun-2009   \n9                         0       64GB  Sep-2008   \n10                        0       64GB  Jul-2008   \n\n                                          Report Link  \n0   https://www.spec.org/jvm2008/results/res2009q4...  \n1   https://www.spec.org/jvm2008/results/res2009q4...  \n2   https://www.spec.org/jvm2008/results/res2009q4...  \n3   https://www.spec.org/jvm2008/results/res2012q1...  \n4   https://www.spec.org/jvm2008/results/res2012q1...  \n5   https://www.spec.org/jvm2008/results/res2010q4...  \n6   https://www.spec.org/jvm2008/results/res2011q4...  \n7   https://www.spec.org/jvm2008/results/res2015q1...  \n8   https://www.spec.org/jvm2008/results/res2009q2...  \n9   https://www.spec.org/jvm2008/results/res2008q3...  \n10  https://www.spec.org/jvm2008/results/res2008q3...  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Company</th>\n      <th>System</th>\n      <th>Result</th>\n      <th># cores</th>\n      <th>Processor</th>\n      <th>CPU Speed(MHz)</th>\n      <th>1st Cache per core(KB)</th>\n      <th>2nd Cache per core(KB)</th>\n      <th>3rd Cache per chip(MB)</th>\n      <th>Other Cache per chip(MB)</th>\n      <th>Memory(GB)</th>\n      <th>Updated</th>\n      <th>Report Link</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>26.4</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>2930</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>3072KB</td>\n      <td>0</td>\n      <td>0</td>\n      <td>4GB</td>\n      <td>Nov-2009</td>\n      <td>https://www.spec.org/jvm2008/results/res2009q4...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>37.4</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>2930</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>3072KB</td>\n      <td>0</td>\n      <td>0</td>\n      <td>4GB</td>\n      <td>Nov-2009</td>\n      <td>https://www.spec.org/jvm2008/results/res2009q4...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Apple Inc.</td>\n      <td>iMac (Early 2009)</td>\n      <td>51.6</td>\n      <td>2</td>\n      <td>Intel Core 2 Duo CPU E8335</td>\n      <td>2930</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>3072KB</td>\n      <td>0</td>\n      <td>0</td>\n      <td>4GB</td>\n      <td>Nov-2009</td>\n      <td>https://www.spec.org/jvm2008/results/res2009q4...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Huawei Technologies Corporation</td>\n      <td>RH 2285</td>\n      <td>336.0</td>\n      <td>12</td>\n      <td>Intel Xeon E5645</td>\n      <td>2400</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>256KB</td>\n      <td>0</td>\n      <td>12MB</td>\n      <td>48GB</td>\n      <td>Jan-2012</td>\n      <td>https://www.spec.org/jvm2008/results/res2012q1...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Oracle Corporation</td>\n      <td>Netra SPARC T4-2</td>\n      <td>455.0</td>\n      <td>16</td>\n      <td>SPARC T4</td>\n      <td>2848</td>\n      <td>16KB(I)+16KB(D)</td>\n      <td>128KB</td>\n      <td>0</td>\n      <td>4MB</td>\n      <td>256GB</td>\n      <td>Jan-2012</td>\n      <td>https://www.spec.org/jvm2008/results/res2012q1...</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Oracle Corporation</td>\n      <td>SPARC T3-2</td>\n      <td>321.0</td>\n      <td>32</td>\n      <td>SPARC T3</td>\n      <td>1650</td>\n      <td>16KB(I)+8KB(D)</td>\n      <td>384KB</td>\n      <td>0</td>\n      <td>0</td>\n      <td>256GB</td>\n      <td>Oct-2010</td>\n      <td>https://www.spec.org/jvm2008/results/res2010q4...</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Oracle Corporation</td>\n      <td>SPARC T4-2</td>\n      <td>454.0</td>\n      <td>16</td>\n      <td>SPARC T4</td>\n      <td>2848</td>\n      <td>16KB(I)+16KB(D)</td>\n      <td>128KB</td>\n      <td>0</td>\n      <td>4MB</td>\n      <td>256GB</td>\n      <td>Nov-2011</td>\n      <td>https://www.spec.org/jvm2008/results/res2011q4...</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Sugon</td>\n      <td>Sugon I620-G20(Intel Xeon E5-2660 v3)</td>\n      <td>853.0</td>\n      <td>20</td>\n      <td>Intel Xeon E5-2660 v3</td>\n      <td>2600</td>\n      <td>32KB(I+D)</td>\n      <td>256KB</td>\n      <td>0</td>\n      <td>25MB</td>\n      <td>256GB</td>\n      <td>Feb-2015</td>\n      <td>https://www.spec.org/jvm2008/results/res2015q1...</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Blade X6270</td>\n      <td>317.0</td>\n      <td>8</td>\n      <td>Intel Xeon X5570</td>\n      <td>2930</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>256KB</td>\n      <td>8MB</td>\n      <td>0</td>\n      <td>48GB</td>\n      <td>Jun-2009</td>\n      <td>https://www.spec.org/jvm2008/results/res2009q2...</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Fire X4450</td>\n      <td>284.0</td>\n      <td>24</td>\n      <td>Intel Xeon X7460</td>\n      <td>2667</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1536KB</td>\n      <td>0</td>\n      <td>0</td>\n      <td>64GB</td>\n      <td>Sep-2008</td>\n      <td>https://www.spec.org/jvm2008/results/res2008q3...</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Sun Microsystems Inc.</td>\n      <td>Sun Fire X4450</td>\n      <td>260.0</td>\n      <td>16</td>\n      <td>Intel Xeon X7350</td>\n      <td>2933</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>2048KB</td>\n      <td>0</td>\n      <td>0</td>\n      <td>64GB</td>\n      <td>Jul-2008</td>\n      <td>https://www.spec.org/jvm2008/results/res2008q3...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ods_jvm2008_data[\"Company\"] = ods_jvm2008_data[\"Company\"].map(company_rule)\n",
    "ods_jvm2008_data[\"Processor\"] = ods_jvm2008_data[\"Processor\"].map(processor_rule)\n",
    "ods_jvm2008_data[\"CPU Speed(MHz)\"] = ods_jvm2008_data[\"CPU Speed\"].map(cpu_speed_rule)\n",
    "ods_jvm2008_data[\"1st Cache per core(KB)\"] = ods_jvm2008_data[\"1st Cache\"].map(first_cache_rule)\n",
    "ods_jvm2008_data[\"2nd Cache per core(KB)\"] = ods_jvm2008_data.apply(second_cache_rule, axis=1)\n",
    "ods_jvm2008_data[\"3rd Cache per chip(MB)\"] = ods_jvm2008_data[\"Other Cache\"].map(third_cache_rule)\n",
    "ods_jvm2008_data[\"Other Cache per chip(MB)\"] = ods_jvm2008_data[\"Other Cache\"].map(other_cache_rule)\n",
    "ods_jvm2008_data[\"Memory(GB)\"] = ods_jvm2008_data[\"Memory\"].map(memory_rule)\n",
    "ods_jvm2008_data[\"Report Link\"] = ods_jvm2008_data[\"Disclosure\"].map(report_link_rule)\n",
    "\n",
    "ods_jvm2008_data = ods_jvm2008_data.drop(columns=[\"# cores per chip\", \"CPU Speed\", \"1st Cache\", \"2nd Cache\", \"Other Cache\", \"Memory\", \"Disclosure\"])\n",
    "\n",
    "ods_order = [\"Company\", \"System\", \"Result\", \"# cores\", \"Processor\", \"CPU Speed(MHz)\", \"1st Cache per core(KB)\", \"2nd Cache per core(KB)\", \"3rd Cache per chip(MB)\", \"Other Cache per chip(MB)\", \"Memory(GB)\", \"Updated\", \"Report Link\"]\n",
    "\n",
    "ods_jvm2008_data:pd.DataFrame = ods_jvm2008_data[ods_order]\n",
    "\n",
    "ods_jvm2008_data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "结果保存为CSV文件"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [],
   "source": [
    "ods_jvm2008_data.to_csv(os.path.join(\"../data/ods\", \"ods_jvm2008.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}